using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using MySql.Data.MySqlClient;
using LdExpress;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Configuration;
using System.Collections;
namespace LdExpress
{
    /// <summary>
    /// TinhTP
    /// </summary>
    public class Tinh
    {
        public long ID = -1;
        public string Ma = "";
        public string Ten = "";
        public long VungID = -1;
        public Tinh()
        {
        }
        public Tinh(long lID)
        {
            this.ID = lID;
        }
        public void Save(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            if (ID <= 0) // new object, we insert new record to database
            {
                // ID = DBModule.GetNewID(typeof(Tinh), "tbl_Tinh", cn, trans);
                // build SQL statement
                strSQL = "Insert into tbl_Tinh (" +
                             "Ma," +
                             "Ten," +
                             "VungID) " +
                         " values(" +
                             "N'" + DBModule.RefineString(Ma) + "'," +
                             "N'" + DBModule.RefineString(Ten) + "'," +
                             VungID.ToString() + ")";
                // run SQL statement
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                UserObjectsLog.SaveObjectActionLog("Tinh", "add", cn, trans);
            }
            else // edit object, we update old record in database
            {
                // build SQL statement
                strSQL = "Update tbl_Tinh set " +
                             "Ma=N'" + DBModule.RefineString(Ma) + "'," +
                             "Ten=N'" + DBModule.RefineString(Ten) + "'," +
                             "VungID=" + VungID.ToString() + "" +
                    " where ID=" + ID.ToString();
                // run SQL statement
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                UserObjectsLog.SaveObjectActionLog("Tinh", "edit", cn, trans);
            }
        }
        public void Delete(MySqlConnection cn, MySqlTransaction trans)
        {
            //Buil SQL Statement
            string strSQL = "DELETE FROM tbl_Tinh WHERE ID = " + this.ID.ToString();
            UserObjectsLog.SaveObjectActionLog("Tinh", "delete", cn, trans);
            //Run SQL Statement
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
        }
        public void Load(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            //Build SQL Statement
            strSQL = "SELECT * FROM tbl_Tinh WHERE ID = " + ID.ToString();
            //Run SQL Statement
            DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            //Fill data into this object
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                if (!dr.IsNull("ID")) ID = long.Parse(dr["ID"].ToString());
                if (!dr.IsNull("Ma")) Ma = dr["Ma"].ToString();
                if (!dr.IsNull("Ten")) Ten = dr["Ten"].ToString();
                if (!dr.IsNull("VungID")) VungID = long.Parse(dr["VungID"].ToString());
            }
        }
        public void Load(string strMa, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            //Build SQL Statement
            if (strMa == "01") strMa = "05";
            strSQL = "SELECT * FROM tbl_Tinh WHERE Ma=N'" + strMa + "'";
            //Run SQL Statement
            DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            //Fill data into this object
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                if (!dr.IsNull("ID")) ID = long.Parse(dr["ID"].ToString());
                if (!dr.IsNull("Ma")) Ma = dr["Ma"].ToString();
                if (!dr.IsNull("Ten")) Ten = dr["Ten"].ToString();
                if (!dr.IsNull("VungID")) VungID = long.Parse(dr["VungID"].ToString());
            }
        }
        public void LoadByName(string strTinh, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            //Build SQL Statement
            strSQL = "SELECT * FROM tbl_Tinh WHERE Ten=N'" + strTinh + "'";
            //Run SQL Statement
            DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            //Fill data into this object
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                if (!dr.IsNull("ID")) ID = long.Parse(dr["ID"].ToString());
                if (!dr.IsNull("Ma")) Ma = dr["Ma"].ToString();
                if (!dr.IsNull("Ten")) Ten = dr["Ten"].ToString();
                if (!dr.IsNull("VungID")) VungID = long.Parse(dr["VungID"].ToString());
            }
        }
        public static void GetList(string strIDs, string VungID, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            NumberOfPages = 0;
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select * from tbl_Tinh where 1=1 ";
            if ((strIDs != "") && (strIDs != null))
            {
                strSQL += " AND ID in (" + strIDs + ")";
            }

            if ((VungID != "") && (VungID != null))
            {
                try
                {
                    int tempVungID = int.Parse(VungID);
                    if (tempVungID > 0)
                        strSQL += " and VungID=" + tempVungID.ToString();
                }
                catch { }
            }
            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            else
                strSQL = strSQL + " Order By Ten";
            // run SQL statement
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, PageNo, out NumberOfPages, cn, trans);
            }
            else
                ds = DBModule.ExecuteQuery(strSQL, cn, trans);
        }
        public void GetList(string strCondition, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            NumberOfPages = 0;
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select tbl_Tinh.*,TBLocation.Name as LocationName  from tbl_Tinh" +
                " Inner Join tbl_location TBLocation on tbl_Tinh.VungID=TBLocation.ID" +
                " where 1=1 ";
            if ((strCondition != null) && (strCondition != ""))
            {
                if (strCondition != "All") strSQL += strCondition;
            }
            if ((OrderBy != null) && (OrderBy != ""))
                strSQL += " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, PageNo, out NumberOfPages, cn, trans);
            }
            else
                ds = DBModule.ExecuteQuery(strSQL, cn, trans);

        }
        public static void doSearch(string strKeywords, string VungID, string OrderBy, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select [ID] from tbl_Tinh where 1=1 ";
            if ((strKeywords != "") && (strKeywords != null))
            {
                strSQL += " AND ( Ma like N'%" + strKeywords + "%' or  Ten like N'%" + strKeywords + "%')";
            }

            if ((VungID != "") && (VungID != null))
            {
                try
                {
                    int tempVungID = int.Parse(VungID);
                    strSQL += " and VungID=" + tempVungID.ToString();
                }
                catch { }
            }
            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            ds = DBModule.ExecuteQuery(strSQL, cn, trans);
        }

        public static string GetNameFromID(long tinhID, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            //Build SQL Statement
            strSQL = "SELECT top 1 Ten FROM tbl_Tinh WHERE ID = " + tinhID.ToString();
            //Run SQL Statement
            DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            //Fill data into this object
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                if (!dr.IsNull("Ten")) return dr["Ten"].ToString();
            }
            return "";
        }
        public static int GetIDFromCode(string code, MySqlConnection cn, MySqlTransaction trans)
        {
            int retVal = 0;
            string strSQL = "";
            //Build SQL Statement
            if (code == "01") code = "05";
            strSQL = "SELECT ID FROM tbl_Tinh WHERE Ma = '" + code + "' limit 0,1";
            //Run SQL Statement
            DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            //Fill data into this object
            if (ds.Tables[0].Rows.Count > 0)
            {
                retVal = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
            }
            return retVal;
        }
        public static string GetListIDByVungIDs(string vungIDs, MySqlConnection cn, MySqlTransaction trans)
        {
            string retVal = "";
            string strSQL = "";
            //Build SQL Statement            
            strSQL = "SELECT ID FROM tbl_Tinh WHERE VungID in (" + vungIDs + ")";
            //Run SQL Statement
            DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            //Fill data into this object
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    if (retVal.Length > 0)
                        retVal += "," + dr["ID"].ToString();
                    else
                        retVal = dr["ID"].ToString();
                }
            }
            return retVal;
        }
    }
}
